Covid plots for family
April 09, 2020
Background
Notes and format last updated Apr 9, 2020
This page shows COVID data broken down into regions that affect our family. It pulls from publicly available raw data from the NY Times, which is reported daily. It’s on a one-day lag, so when you see this report, the most recent day included will be the day prior.
The page starts by showing growth rates. I’ve moved down the raw numbers to later in the page because you can see them everywhere, and it’s sometimes hard to understand trends looking at them. Growth rates seem to me to be more important for understanding our progress.
- For the U.S. and each of our states, we show two growth rate charts: confirmed cases (total and new) and deaths (total and new). Each chart has two lines: a growth-rate in the rolling three-day total of cases (or deaths) and a growth rate in a rolling three-day average of new cases (or deaths).
- For total cases (or deaths), the charts show the growth rate in total cases (or deaths) over the previous three days for each respective date. For example, a three-day growth rate of 100% for total cases means that total cases are doubling every three days. This rate can’t go below zero (because total cases (or deaths) can never go down), but we want it to get as close to zero as possible.
- For new cases (or deaths), the charts show the daily growth rate in the average number of new cases (or deaths) for the previous three days. This rate can go below zero because we’ll eventually start to see consistently less new cases (or deaths) each day. When the new case (or death) growth rate is consistently below zero, it means we’re on the other side of the “apex.”
- For the U.S. and each of our states, we show two growth rate charts: confirmed cases (total and new) and deaths (total and new). Each chart has two lines: a growth-rate in the rolling three-day total of cases (or deaths) and a growth rate in a rolling three-day average of new cases (or deaths).
The page then shows state and county rankings normalized for population (number of cases or deaths per million residents).
- For states, it shows tables ranking all 50 states for per populations rates of total cases, new cases, total deaths, and new deaths. In addition to the tables, the trends for the top states are plotted over time.
- For counties, it shows tables with our counties ranked by percentile of U.S. counties for per population rates of total cases and total deaths. The table also shows the top counties in the country for perspecive. And there is then a plot showing our counties’ percentiles over time.
There is then a long section with raw counts for the U.S., our states, and our counties for each of these categories:
- Total confirmed cases: This is the cumulative total of cases reported.
- New confirmed cases: This is the number of new cases reported on each respective date.
- Total deaths: This is the cumulative total of deaths reported.
- New deaths: This is the number of new deaths reported on each respective date.
- Rolling three-day death growth rate: This is the same as described for total and new cases above, but for total and new deaths.
Finally, the last section compares data from states that have and have not enacted state-wide stay-at-home orders, trying to see if there are any effects of the orders. It’s a work in progress, and there are more extensive notes in that section.
Growth rates
Confirmed cases (total and new)
U.S.
Our states
Deaths (total and new)
U.S.
Our states
By population rankings
States
Total confirmed cases
Table of total confirmed cases per million residents (all 50 states)
## state casesPerMillion
## 1: New York 7,679
## 2: New Jersey 5,340
## 3: Louisiana 3,663
## 4: Connecticut 2,462
## 5: Massachusetts 2,435
## 6: District of Columbia 2,040
## 7: Michigan 2,024
## 8: Rhode Island 1,368
## 9: Pennsylvania 1,282
## 10: Washington 1,194
## 11: Illinois 1,189
## 12: Delaware 1,146
## 13: Colorado 981
## 14: Vermont 969
## 15: Georgia 961
## 16: Maryland 914
## 17: Indiana 882
## 18: Nevada 752
## 19: Florida 730
## 20: Idaho 689
## 21: Mississippi 673
## 22: Tennessee 621
## 23: Utah 584
## 24: New Hampshire 579
## 25: Missouri 542
## 26: Alabama 509
## 27: South Carolina 495
## 28: California 481
## 29: Wisconsin 473
## 30: South Dakota 444
## 31: Ohio 440
## 32: Virginia 427
## 33: New Mexico 412
## 34: Maine 399
## 35: Wyoming 397
## 36: Oklahoma 385
## 37: Arizona 374
## 38: Iowa 362
## 39: Kansas 362
## 40: Arkansas 356
## 41: Texas 341
## 42: North Dakota 329
## 43: North Carolina 326
## 44: Kentucky 311
## 45: Montana 310
## 46: Alaska 306
## 47: Hawaii 305
## 48: Oregon 293
## 49: Nebraska 278
## 50: West Virginia 269
## 51: Minnesota 204
## 52: Puerto Rico 194
## 53: Guam <NA>
## 54: Northern Mariana Islands <NA>
## 55: Virgin Islands <NA>
## state casesPerMillion
New confirmed cases
Table of new cases per million residents: rolling 3-day total (all 50 states)
## state casesNewPerMillion
## 1: New York 1,361
## 2: New Jersey 1,118
## 3: Connecticut 871
## 4: Louisiana 864
## 5: District of Columbia 626
## 6: Massachusetts 622
## 7: Rhode Island 498
## 8: Michigan 459
## 9: Delaware 454
## 10: Pennsylvania 376
## 11: Georgia 326
## 12: Maryland 317
## 13: Illinois 300
## 14: Indiana 227
## 15: South Dakota 172
## 16: Missouri 156
## 17: Florida 155
## 18: Vermont 149
## 19: Washington 146
## 20: Nevada 142
## 21: Alabama 134
## 22: Colorado 122
## 23: Mississippi 122
## 24: Virginia 118
## 25: New Mexico 114
## 26: Tennessee 103
## 27: California 100
## 28: Kansas 100
## 29: South Carolina 97
## 30: Texas 96
## 31: Ohio 94
## 32: West Virginia 88
## 33: Iowa 87
## 34: New Hampshire 87
## 35: Utah 84
## 36: Nebraska 83
## 37: Wisconsin 83
## 38: North Carolina 79
## 39: Kentucky 77
## 40: Arkansas 74
## 41: Idaho 73
## 42: Oklahoma 68
## 43: Arizona 62
## 44: North Dakota 57
## 45: Alaska 53
## 46: Wyoming 51
## 47: Maine 49
## 48: Hawaii 45
## 49: Puerto Rico 45
## 50: Oregon 40
## 51: Minnesota 38
## 52: Montana 31
## 53: Guam <NA>
## 54: Northern Mariana Islands <NA>
## 55: Virgin Islands <NA>
## state casesNewPerMillion
Total deaths
Table of total deaths per million residents (all 50 states)
## state deathsPerMillion
## 1: New York 322
## 2: New Jersey 169
## 3: Louisiana 140
## 4: Michigan 96
## 5: Connecticut 93
## 6: Massachusetts 62
## 7: Washington 59
## 8: District of Columbia 38
## 9: Illinois 36
## 10: Vermont 36
## 11: Georgia 34
## 12: Colorado 33
## 13: Rhode Island 33
## 14: Indiana 30
## 15: Nevada 25
## 16: Pennsylvania 24
## 17: Mississippi 22
## 18: Maryland 20
## 19: Delaware 19
## 20: Oklahoma 19
## 21: Wisconsin 18
## 22: Kentucky 16
## 23: Ohio 16
## 24: Florida 14
## 25: Missouri 14
## 26: Alabama 13
## 27: Kansas 13
## 28: New Hampshire 13
## 29: California 12
## 30: South Carolina 12
## 31: Tennessee 12
## 32: Arizona 10
## 33: Idaho 10
## 34: Maine 10
## 35: Oregon 9
## 36: Iowa 8
## 37: Virginia 8
## 38: Nebraska 7
## 39: New Mexico 7
## 40: Puerto Rico 7
## 41: Alaska 6
## 42: Minnesota 6
## 43: South Dakota 6
## 44: Texas 6
## 45: Arkansas 5
## 46: Montana 5
## 47: North Carolina 5
## 48: North Dakota 5
## 49: Utah 4
## 50: Hawaii 3
## 51: West Virginia 2
## 52: Wyoming 0
## 53: Guam <NA>
## 54: Northern Mariana Islands <NA>
## 55: Virgin Islands <NA>
## state deathsPerMillion
New deaths
Table of new deaths per million residents: rolling 3-day total (all 50 states)
## state deathsNewPerMillion
## 1: New York 108
## 2: New Jersey 66
## 3: Connecticut 40
## 4: Louisiana 37
## 5: Michigan 34
## 6: Massachusetts 29
## 7: Georgia 14
## 8: Illinois 14
## 9: Washington 14
## 10: Pennsylvania 12
## 11: Indiana 11
## 12: Nevada 11
## 13: Colorado 9
## 14: Maryland 9
## 15: Rhode Island 9
## 16: Mississippi 8
## 17: Oklahoma 8
## 18: District of Columbia 7
## 19: Kentucky 6
## 20: New Hampshire 6
## 21: Ohio 6
## 22: Delaware 5
## 23: Idaho 5
## 24: Kansas 5
## 25: Missouri 5
## 26: Wisconsin 5
## 27: Alabama 4
## 28: Florida 4
## 29: South Dakota 4
## 30: Tennessee 4
## 31: California 3
## 32: Nebraska 3
## 33: South Carolina 3
## 34: Arizona 2
## 35: Maine 2
## 36: North Carolina 2
## 37: Oregon 2
## 38: Texas 2
## 39: Virginia 2
## 40: Alaska 1
## 41: Iowa 1
## 42: Minnesota 1
## 43: New Mexico 1
## 44: North Dakota 1
## 45: Puerto Rico 1
## 46: Utah 1
## 47: Vermont 1
## 48: Arkansas 0
## 49: Hawaii 0
## 50: Montana 0
## 51: West Virginia 0
## 52: Wyoming 0
## 53: Guam <NA>
## 54: Northern Mariana Islands <NA>
## 55: Virgin Islands <NA>
## state deathsNewPerMillion
Counties
Confirmed cases
Table showing total cases per million and percentile for all US counties Includes our counties and the top 5 in the US for perspective
# merge county raw and county pop
county.per.cap <- merge(county.raw, county_pop, all.x = T, by.x = c("county", "state"), by.y = c("CTYNAME", "STNAME"))
county.per.cap[, casesPerMillion := round(cases/POPESTIMATE2019 * 1000000, 2)]
date.cut <- max(county.per.cap$date)
county.per.cap <- county.per.cap[date == date.cut]
county.per.cap <- county.per.cap[order(-casesPerMillion), c("county", "state", "casesPerMillion")]
county.per.cap[, rawRanking := .I]
county.per.cap[, percentile := floor((3142 - rawRanking)/31.42)]
county.per.cap[, casesPerMillion := comma(casesPerMillion)]
county.per.cap[, flgOurCounty := ifelse(county %in% "Pierce" & state %in% "Washington", 1,
ifelse(county %in% "Orange" & state %in% "California", 1,
ifelse(county %in% c("Richland", "York") & state %in% "South Carolina", 1,
ifelse(county %in% "Davidson" & state %in% "Tennessee", 1, 0))))]
county.per.cap[flgOurCounty == 1 | rawRanking < 6, -c("flgOurCounty")]## county state casesPerMillion rawRanking percentile
## 1: Rockland New York 19,685 1 99
## 2: Blaine Idaho 18,592 2 99
## 3: Westchester New York 16,421 3 99
## 4: Nassau New York 13,669 4 99
## 5: Randolph Georgia 12,836 5 99
## 6: Davidson Tennessee 1,642 116 96
## 7: Richland South Carolina 895 308 90
## 8: Pierce Washington 885 313 90
## 9: York South Carolina 409 839 73
## 10: Orange California 320 1071 65Our county percentiles over time
county.per.cap <- merge(county.raw, county_pop, all.x = T, by.x = c("county", "state"), by.y = c("CTYNAME", "STNAME"))
county.per.cap[, casesPerMillion := round(cases/POPESTIMATE2019 * 1000000, 2)]
county.per.cap <- county.per.cap[order(fips, date),
.(casesPerMillion = max(casesPerMillion)),
by = c("fips", "date")]
county.per.cap <- county.per.cap[!is.na(casesPerMillion) & as.Date(date) >= "2020-03-16"]
county.per.cap.casted <- dcast.data.table(county.per.cap, factor(fips) ~ factor(date), value.var = "casesPerMillion", fun.aggregate = sum)
for(i in seq.Date(as.Date("2020-03-16"), max(as.Date(county.raw$date)), by = "day")) {
# i <- "2020-04-07"
i <- format(as.Date(i, origin = "1970-01-01"), "%Y-%m-%d")
colnames <- c("fips", as.character(i))
temp <- county.per.cap.casted[, ..colnames]
setnames(temp, as.character(i), "casesPerMillion")
temp <- temp[order(-casesPerMillion)]
temp[, rawRanking := .I]
temp[, paste0("percentile_", as.character(i)) := floor((3142 - rawRanking)/31.42)]
county.per.cap.casted <- merge(county.per.cap.casted, temp[, c(1,4)], all.x = T, by = "fips")
}
setnames(county.per.cap.casted, "fips", "percentile_fips")
county.per.cap.casted <- county.per.cap.casted[, .SD, .SDcols = patterns("^percentile")]
county.per.cap.melt <- melt(county.per.cap.casted, id.var = "percentile_fips")
county.per.cap.melt[, variable := gsub("percentile_(.*)", "\\1", variable)]
our.fips <- c(6059, 47037, 53053, 45091, 45079)
county.per.cap.melt <- county.per.cap.melt[percentile_fips %in% our.fips]
county.per.cap.melt[, county := county.raw$county[match(county.per.cap.melt$percentile_fips, county.raw$fips)]]
plot.county.per.cap <- ggplot(county.per.cap.melt, aes(x = as.Date(variable), y = value, color = county)) +
geom_line(aes(group = county)) +
labs(x = "Date",
y = "Percentile of US counties",
title = "Our county percentiles for total COVID cases",
subtitle = "Percentiles based on total cases per million residents",
color = "County") +
theme(plot.title = element_text(hjust = 0.5),
plot.subtitle = element_text(hjust = 0.5))
plot.county.per.cap
Deaths
Table showing total deaths per million and percentile for all US counties Includes our counties and the top 5 in the US for perspective
county.per.cap <- merge(county.raw, county_pop, all.x = T, by.x = c("county", "state"), by.y = c("CTYNAME", "STNAME"))
county.per.cap[, deathsPerMillion := round(deaths/POPESTIMATE2019 * 1000000, 2)]
date.cut <- max(county.per.cap$date)
county.per.cap <- county.per.cap[date == date.cut]
county.per.cap <- county.per.cap[order(-deathsPerMillion), c("county", "state", "deathsPerMillion")]
county.per.cap[, rawRanking := .I]
county.per.cap[, percentile := floor((3142 - rawRanking)/31.42)]
county.per.cap[, deathsPerMillion := comma(deathsPerMillion)]
county.per.cap[, flgOurCounty := ifelse(county %in% "Pierce" & state %in% "Washington", 1,
ifelse(county %in% "Orange" & state %in% "California", 1,
ifelse(county %in% c("Richland", "York") & state %in% "South Carolina", 1,
ifelse(county %in% "Davidson" & state %in% "Tennessee", 1, 0))))]
county.per.cap[flgOurCounty == 1 | rawRanking < 6, -c("flgOurCounty")]## county state deathsPerMillion rawRanking percentile
## 1: Terrell Georgia 1,055 1 99
## 2: Dougherty Georgia 705 2 99
## 3: Greer Oklahoma 700 3 99
## 4: Baker Georgia 658 4 99
## 5: Toole Montana 633 5 99
## 6: Richland South Carolina 24 425 86
## 7: Davidson Tennessee 19 499 84
## 8: Pierce Washington 18 521 83
## 9: York South Carolina 7 737 76
## 10: Orange California 5 786 74Our county percentiles over time
county.per.cap <- merge(county.raw, county_pop, all.x = T, by.x = c("county", "state"), by.y = c("CTYNAME", "STNAME"))
county.per.cap[, deathsPerMillion := round(deaths/POPESTIMATE2019 * 1000000, 2)]
county.per.cap <- county.per.cap[order(fips, date),
.(deathsPerMillion = max(deathsPerMillion)),
by = c("fips", "date")]
county.per.cap <- county.per.cap[!is.na(deathsPerMillion) & as.Date(date) >= "2020-03-16"]
county.per.cap.casted <- dcast.data.table(county.per.cap, factor(fips) ~ factor(date), value.var = "deathsPerMillion", fun.aggregate = sum)
for(i in seq.Date(as.Date("2020-03-16"), max(as.Date(county.raw$date)), by = "day")) {
# i <- "2020-04-07"
i <- format(as.Date(i, origin = "1970-01-01"), "%Y-%m-%d")
colnames <- c("fips", as.character(i))
temp <- county.per.cap.casted[, ..colnames]
setnames(temp, as.character(i), "deathsPerMillion")
temp <- temp[order(-deathsPerMillion)]
temp[, rawRanking := .I]
temp[, paste0("percentile_", as.character(i)) := floor((3142 - rawRanking)/31.42)]
county.per.cap.casted <- merge(county.per.cap.casted, temp[, c(1,4)], all.x = T, by = "fips")
}
setnames(county.per.cap.casted, "fips", "percentile_fips")
county.per.cap.casted <- county.per.cap.casted[, .SD, .SDcols = patterns("^percentile")]
county.per.cap.melt <- melt(county.per.cap.casted, id.var = "percentile_fips")
county.per.cap.melt[, variable := gsub("percentile_(.*)", "\\1", variable)]
our.fips <- c(6059, 47037, 53053, 45091, 45079)
county.per.cap.melt <- county.per.cap.melt[percentile_fips %in% our.fips]
county.per.cap.melt[, county := county.raw$county[match(county.per.cap.melt$percentile_fips, county.raw$fips)]]
plot.county.per.cap <- ggplot(county.per.cap.melt, aes(x = as.Date(variable), y = value, color = county)) +
geom_line(aes(group = county)) +
labs(x = "Date",
y = "Percentile of US counties",
title = "Our county percentiles for total COVID deaths",
subtitle = "Percentiles based on total deaths per million residents",
color = "County") +
theme(plot.title = element_text(hjust = 0.5),
plot.subtitle = element_text(hjust = 0.5))
plot.county.per.cap
Raw counts
Total confirmed cases
U.S.
Our states
Our counties
New confirmed cases
U.S.
Our states
Our counties
Total deaths
U.S.
Our states
Our counties
New deaths
U.S.
Our states
Our counties
Stay-at-home comparisons
- The section is a work in progress – I’ll probably change and add plots over the coming days.
- I’m trying to see what effect stay-at-home orders have on spread. It’s inherentely messy because some states have localized orders but not orders for their full state and because states implemented the orders at different times.
- The current plot shows the rolling 3-day average of new cases per million residents, averaged across 2 groups of states – those with a state-wide stay-at-home order in effect and those without. The size of the dots reflect the number of states in each group for each given day (so the dots are getting bigger for the group with orders over time, as more states implement those orders).
- So far, this view doesn’t show if these orders are “working” or not. Instead, the biggest takeaway from the current plot is that the states that implemented orders are reacting to having worse spread, which is why the case rate is so much higher at the start for them. As a state decides it has a problem and implements an order, it joins the grouping of states with orders and drives the rate up for that group.
- Over time (if the policies work), what we’d expect is that the line for the group with orders flattens a bit, but we’ll see. It also may become moot if all states issue orders.
- The next plot I want to add will show rate of spread based on how long an order has been in effect, but still thinking about how to do this. Mostly, I’m trying to decide what the right baseline is to compare against.
